IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GenMSSQLMemory' AND type = 'P')
     DROP PROCEDURE dbo.GenMSSQLMemory
GO

CREATE PROCEDURE dbo.GenMSSQLMemory
     @PackageExecutionId tinyint,
     @DebugFlag          tinyint = 0
AS
/*
 *
 * GenMSSQLMemory
 *
 * Purpose:      To determine the memory share of all running instances for 
 *               a cluster or machine.
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/03/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @MemoryShare TABLE ( SrvrInstncId int          NOT NULL PRIMARY KEY CLUSTERED, 
                                  MachineName  varchar(128) NOT NULL,
                                  MemoryShare  decimal(5,1) NOT NULL, 
                                  MinMemMB     int              NULL, 
                                  MaxMemMB     int              NULL )

     DECLARE @Machine TABLE ( MachineId             int          NOT NULL PRIMARY KEY CLUSTERED, 
                              MachineName           varchar(128) NOT NULL, 
                              TotalMachineMemoryMB  int              NULL, 
                              RSVDMachineMemoryMB   int              NULL,
                              AvailableMemoryMB     int              NULL,
                              TotalSharesForMachine decimal(5,1)     NULL,
                              MBPerShare            int              NULL )

     -- Get memory shares configuration
     INSERT @MemoryShare
          ( SrvrInstncId, 
            MachineName,
            MemoryShare )
     SELECT DISTINCT 
            t.SrvrInstncId,
            t.MachineName,
            MemoryShare = COALESCE( simmc.MemoryShare, 1.0 )
     FROM   tempdb.dbo.tempMSSQLRunningMachine t
            LEFT OUTER JOIN dbo.SrvrInstncMemoryCfgMSSQL simmc
               ON t.SrvrInstncId = simmc.SrvrInstncId
     WHERE  t.SrvrInstncId       != -1
     AND    t.PackageExecutionId  = @PackageExecutionId

     INSERT @Machine
          ( MachineId, 
            MachineName,
            TotalMachineMemoryMB,
            RSVDMachineMemoryMB,
            AvailableMemoryMB )
     SELECT DISTINCT 
            m.MachineId,
            t.MachineName,
            m.TotalMachineMemoryMB,
            m.RSVDMachineMemoryMB,
            m.TotalMachineMemoryMB - m.RSVDMachineMemoryMB
     FROM   dbo.Machine                        m,
            tempdb.dbo.tempMSSQLRunningMachine t
     WHERE  ( m.MachineName      = t.MachineName
           OR m.FQMachineName    = t.MachineName )
     AND    t.PackageExecutionId = @PackageExecutionId

     -- Verify that we have a record for all machine names identified
     IF EXISTS ( SELECT 'X'
                 FROM   tempdb.dbo.tempMSSQLRunningMachine t
                 WHERE  NOT EXISTS ( SELECT 'X'
                                     FROM   @Machine m
                                     WHERE  m.MachineName = t.MachineName )
                 AND    t.SrvrInstncId       != -1
                 AND    t.PackageExecutionId  = @PackageExecutionId )
     BEGIN -- We have at least one machine that was not matched so we are unable
           -- to reliably set server instance memory.
          RAISERROR( 'We were unable to find machine matches for all servers.  Unable to continue!', 16, 1 )
          PRINT 'Matched List'
          SELECT * FROM @Machine
          RETURN 1
     END

     -- Get total shares allocated and MB per share
     UPDATE @Machine 
     SET    TotalSharesForMachine = ms.TotalMemoryShare,
            MBPerShare            = m.AvailableMemoryMB / ms.TotalMemoryShare
     FROM   @Machine m,
            ( SELECT MachineName, TotalMemoryShare = SUM( MemoryShare )
              FROM   @MemoryShare
              GROUP BY MachineName ) ms
     WHERE   m.MachineName = ms.MachineName

     -- Assign Memory to SQL Instances.
     UPDATE @MemoryShare
     SET    MinMemMB = ( ms.MemoryShare * m.MBPerShare ) * 0.25,
            MaxMemMB = ms.MemoryShare * m.MBPerShare
     FROM   @Machine m INNER JOIN @MemoryShare ms
               ON m.MachineName = ms.MachineName 

     IF ( @DebugFlag = 1 )
     BEGIN
          PRINT '@Machine'
          SELECT * FROM @Machine

          PRINT '@MemoryShare'
          SELECT * FROM @MemoryShare
     END

     SELECT SrvrInstncId, 
            MinMemMB, 
            MaxMemMB, 
            MachineName, 
            MemoryShare
     FROM   @MemoryShare
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Calculates the min and max memory for all SQL instances in a SQL cluster or ', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = GenMSSQLMemory;
GO
